package com.cgt.web.dao.impl;

import com.cgt.web.dao.ITea1Dao;
import com.cgt.web.pojo.Teacher;
import com.cgt.web.pojo.query.Teacher1Query;
import com.cgt.web.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Tea1DaoImpl implements ITea1Dao {
	@Override
	public void deleteById(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM teacher WHERE id = ?";
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(statement);
			int count = statement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally {
			JDBCUtil.close(connection,statement,null);
		}
	}

	@Override
	public void update(Teacher teacher) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE teacher SET name=?,age=?,address=? WHERE id =?";
			statement = connection.prepareStatement(sql);
			statement.setString(1,teacher.getName());
			statement.setInt(2, teacher.getAge());
			statement.setString(3,teacher.getAddress());
			statement.setInt(4, teacher.getId());
			System.out.println(statement);
			int count = statement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(connection,statement,null);
		}
	}

	@Override
	public Teacher selectById(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Teacher teacher = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT name,age,address FROM teacher WHERE id = ?";
			statement=connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(statement);
			resultSet = statement.executeQuery();
			while(resultSet.next()){
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String address = resultSet.getString("address");
				teacher = new Teacher(id,name,age,address);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally {
			JDBCUtil.close(connection,statement,resultSet);
		}
		return teacher;
	}

	@Override
	public void add(Teacher teacher) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection =JDBCUtil.getConnection();
			String sql = "INSERT INTO teacher(name,age,address) VALUES(?,?,?)";
			statement = connection.prepareStatement(sql);
			statement.setString(1,teacher.getName());
			statement.setInt(2, teacher.getAge());
			statement.setString(3,teacher.getAddress());
			System.out.println(statement);
			int count = statement.executeUpdate();
			System.out.println(count);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}finally {
			JDBCUtil.close(connection,statement,null);
		}
	}

	@Override
	public List<Teacher> selectByPage(Teacher1Query teacher1Query) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Teacher> list = new ArrayList<>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT id,name,age,address FROM teacher WHERE 1=1";
			List<Object> queryList = new ArrayList<>();
			String queryName = teacher1Query.getName();
			if(queryName != null && !"".equals(queryName)){
				sql += " AND name LIKE ?";
				queryList.add("%" + queryName + "%");
			}
			String queryAge = teacher1Query.getAge();
			if(queryAge != null && !"".equals(queryAge)){
				sql += " AND age LIKE?";
				queryList.add(queryAge);
			}
			String queryAddress = teacher1Query.getAddress();
			if(queryAddress != null && !"".equals(queryAddress)){
				sql += " AND address=?";
				queryList.add(queryAddress);
			}
			sql+=" LIMIT ?,?";
			int offset = (teacher1Query.getPage() - 1) * teacher1Query.getLimit();
			queryList.add(offset);
			queryList.add(teacher1Query.getLimit());
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < queryList.size(); i++) {
				statement.setObject(i+1,queryList.get(i));
			}
			System.out.println(statement);
			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String address = resultSet.getString("address");
				Teacher teacher = new Teacher(id,name,age,address);
				list.add(teacher);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return list;
	}

	@Override
	public Integer selectTotalCount(Teacher1Query teacher1Query) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		int totalCount = 0;
		try {
			connection = JDBCUtil.getConnection();//建立连接，获取连接对象connection
			String sql = "SELECT count(*) FROM teacher WHERE 1=1";//写sql语句
			List<Object> queryList = new ArrayList<>();
			String queryName = teacher1Query.getName();
			if(queryName != null && !"".equals(queryName)){
				sql += " AND name LIKE ?";
				queryList.add("%" + queryName + "%");
			}
			String queryAge = teacher1Query.getAge();
			if(queryAge != null && !"".equals(queryAge)){
				sql += " AND age LIKE?";
				queryList.add(queryAge);
			}
			String queryAddress = teacher1Query.getAddress();
			if(queryAddress != null && !"".equals(queryAddress)){
				sql += " AND address=?";
				queryList.add(queryAddress);
			}
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < queryList.size(); i++) {
				statement.setObject(i+1,queryList.get(i));
			}
			System.out.println(statement);
			resultSet = statement.executeQuery();//执行查询类sql语句(executeQuery)
			while (resultSet.next()) {
				totalCount = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JDBCUtil.close(connection, statement, resultSet);
		}
		return totalCount;
	}
}
